package database;
import java.sql.*;
import java.util.*;

import common.*;


public class AuthorManager {

	/**
	 * get all authors
	 * param ()
	 * return list of all authors in our DB
	 */
	public static ArrayList<Author> getAllAuthors(){
		
		DBManager db = DBManager.getInstance();
		ArrayList<Author> authorArr= new ArrayList<Author>();
		Author temp =new Author();
		try {
			Statement stmt= db.conn.createStatement();
			ResultSet rs= stmt.executeQuery("SELECT * FROM project.author;");
			while(rs.next()){
				temp=new Author();
				temp.setId(rs.getInt("idAuthor"));
				temp.setName(rs.getString("nameAuthor"));
				temp.setLastName(rs.getString("lastnameAuthor"));
				authorArr.add(temp);
			}//while
			rs.close();
			return authorArr;
		} catch (SQLException e) {
			System.out.println("EXEPTION: getAllAuthors function exeption!");
			return null;
		}//catch
	
	}//getAllAuthors
	
	public static ArrayList<Integer> getAuthorsWhichWroteBooks(){
		DBManager db = DBManager.getInstance();
		ArrayList<Integer> authorIdArr= new ArrayList<Integer>();
		Integer temp ;
		
          Statement stmt;
		try {
			stmt = db.conn.createStatement();
			ResultSet rs= stmt.executeQuery("SELECT DISTINCT author_idAuthor FROM project.book_has_author;");
			while(rs.next()){
				temp=rs.getInt("author_idAuthor");
				authorIdArr.add(temp);
			}//while
			rs.close();
			return authorIdArr;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			System.out.println("EXEPTION: getAuthorsWhichWroteBooks function exeption!");
			return null;
			
		}
         

	}

   public static Author getAuthorById(int id){
	   DBManager db = DBManager.getInstance();
	   Author temp;
	   Statement stmt;
	   try {
		stmt = db.conn.createStatement();
		ResultSet rs= stmt.executeQuery("SELECT * FROM project.author WHERE idAuthor='"+id+"';");
		rs.next();
	    temp = new Author();
	    temp.setId(rs.getInt("idAuthor"));
	    temp.setName(rs.getString("nameAuthor"));
	    temp.setLastName(rs.getString("lastnameAuthor"));
	    rs.close();
	    return temp;
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		System.out.println("EXEPTION: getAuthorById function exeption!");
		e.printStackTrace();
		return null;
	}
	   
	   
   }

   

   
   /**
    * add author to db
    * @param a author
    * @return if author addition successfully return true else return false
    */
   
   public static boolean addAuthor(Author a){
   
	   DBManager db = DBManager.getInstance();

	   try {
		
		String sql ="INSERT INTO project.author (`idAuthor`,`nameAuthor`,`lastnameAuthor`) " +
				"VALUES(?,?,?);";
         PreparedStatement stmt = db.conn.prepareStatement(sql);
         	stmt.setInt(1, a.getId());
         	stmt.setString(2, a.getName());
         	stmt.setString(3, a.getLastName());

         	stmt.executeUpdate();

         	return true;
	} catch (SQLException e) {
		// TODO Auto-generated catch block
		System.out.println("EXEPTION: addAuthor function exeption!");
		return false;
	}
	   
   }
   
   /**
    * remove chosen author from db
    * @param author id
    * @return if removal of author from db was successfully return true else return false
    */
   public static boolean deleteAuthor(int id){
	   DBManager db = DBManager.getInstance();
	   String sql=new String();
	   Statement stmt3;
	   ArrayList<Integer> bookId=new ArrayList<Integer>();
	  // ArrayList<Integer> authorId=new ArrayList<Integer>();
	   
	   
	   
	   
	   sql="SELECT * FROM project.book_has_author WHERE author_idAuthor='"+ id+"';";
	   
	   try {
			stmt3 = db.conn.createStatement();
			ResultSet rs= stmt3.executeQuery(sql);
			while(rs.next()){
				bookId.add(rs.getInt("book_idBook"));
				//authorId.add(rs.getInt("author_idAuthor"));
			}
			rs.close();
			
			
			   sql="DELETE FROM project.book_has_author WHERE author_idAuthor=?;";
			   PreparedStatement stmt1;
				   stmt1=db.conn.prepareStatement(sql);
				   stmt1.setInt(1, id);
				   stmt1.executeUpdate();

	   sql="DELETE FROM project.book WHERE idBook=?;";
	   PreparedStatement stmt2;
	   for(int i=0;i<bookId.size();i++){
		   stmt2=db.conn.prepareStatement(sql);
		   stmt2.setInt(1, bookId.get(i));
		   stmt2.executeUpdate();
	   }

	   
	   
		sql ="DELETE FROM project.author "+
			 "WHERE idAuthor=?;";
			PreparedStatement stmt;
				stmt = db.conn.prepareStatement(sql);
				stmt.setInt(1, id);
				stmt.executeUpdate();
				return true;
			
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				System.out.println("EXEPTION: AuthorManager.deleteAuthor() function exception!");
				e.printStackTrace();
				return false;
			}
	   }
   }